﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IDAL;
using Model;
using Microsoft.ApplicationBlocks.Data;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL
{
    public class CardTypeDAL : ICardTypeDAL
    {
        private static readonly string SQL_CONN_SQLSERVER = ConfigurationManager.AppSettings["DBConn"];

        /// <summary>
        /// 根据传入的cardTypeId判断一条数据是否存在
        /// </summary>       
        public bool cardTypeIdExists(int cardTypeId)
        {
            string SQL_SELECT_CARDTYPE = "select * from CardType where CT_cardTypeId=@cardTypeId";
            bool isExist = false;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@cardTypeId", cardTypeId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_CARDTYPE, para))
                {
                    if (rdr.Read())
                        isExist = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isExist;
        }


        /// <summary>
        /// 根据传入的cardType判断一条数据是否存在
        /// </summary>       
        public bool cardTypeExists(int cardType)
        {
            string SQL_SELECT_CARDTYPE = "select * from CardType where CT_cardType = @cardType";
            bool isExist = false;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@cardType", cardType);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_CARDTYPE, para))
                {
                    if (rdr.Read())
                        isExist = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isExist;
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>                
        public bool Add(int cardTypeId, int cardType, int cardValue, string cardInfo)
        {

            bool operation = false;
            string SQL_INSERT_CARDTYPE = "INSERT INTO CardType VALUES( @cardType, @cardValue,@cardInfo)";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@cardType", cardType), new SqlParameter("@cardValue", cardValue), new SqlParameter("@cardInfo", cardInfo) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_INSERT_CARDTYPE, paras);
                {
                    if (lineNum > 0)
                        operation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return operation;
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>             
        public bool Update(int cardTypeId, int cardType, int cardValue, string cardInfo)
        {
            bool operation = false;
            string sqlStatement = "UPDATE CardType SET CT_cardType = @cardType, CT_cardValue = @cardValue ,CT_cardInfo=@cardInfo WHERE CT_cardTypeId = @cardTypeId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@cardType", cardType), new SqlParameter("@cardValue", cardValue), new SqlParameter("@cardInfo", cardInfo), new SqlParameter("@cardTypeId", cardTypeId)};

                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, paras);
                {
                    if (lineNum > 0)
                        operation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return operation;
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>                
        public bool Add(Model.CardType cardType)
        {
            bool operation = false;
            string SQL_INSERT_CARDTYPES = "INSERT INTO CardType VALUES( @cardType, @cardValue,@cardInfo)";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);
                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@cardType", cardType.getCT_cardType()), new SqlParameter("@cardValue", cardType.getCT_cardValue()), new SqlParameter("@cardInfo", cardType.getCT_cardInfo()) };
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_INSERT_CARDTYPES, paras);
                {
                    if (lineNum > 0)
                        operation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return operation;
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>             
        public bool Update(Model.CardType cardType)
        {
            bool operation = false;
            string sqlStatement = "UPDATE CardType SET CT_cardType = @cardType, CT_cardValue = @cardValue ,CT_cardInfo=@cardInfo WHERE CT_cardTypeId = @cardTypeId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@cardType", cardType.getCT_cardType()), new SqlParameter("@cardValue", cardType.getCT_cardValue()), new SqlParameter("@cardInfo", cardType.getCT_cardInfo()), new SqlParameter("@cardTypeId", cardType.getCT_cardTypeId()) };

                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, paras);
                {
                    if (lineNum > 0)
                        operation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return operation;
        }



        /// <summary>
        /// 根据传入的cardTypeId删除一条数据
        /// </summary>
        public bool cardTypeIdDelete(int cardTypeId)
        {
            bool operation = false;
            string sqlStatement = "DELETE FROM CardType WHERE CT_cardTypeId = @cardTypeId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@cardTypeId", cardTypeId);

                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, para);
                {
                    if (lineNum > 0)
                        operation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return operation;
        }


        /// <summary>
        /// 根据传入的cardType删除一条数据
        /// </summary>
        public bool cardTypeDelete(int cardType)
        {
            bool operation = false;
            string sqlStatement = "DELETE FROM CardType WHERE CT_cardType = @cardType";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@cardType", cardType);

                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, para);
                {
                    if (lineNum > 0)
                        operation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return operation;
        }


        /// <summary>
        /// 根据cardId得到一个对象实体
        /// </summary>
        public Model.CardType GetModel(int cardTypeId)
        {
            Model.CardType cardType = new Model.CardType();
            string SQL_SELECT_CARDNUMBER = "SELECT * FROM CardType WHERE CT_cardTypeId = @cardTypeId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@cardTypeId", cardTypeId);

                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_CARDNUMBER, para))
                {
                    if (rdr.Read())
                    {
                        cardType.setCT_cardTypeId(rdr.GetInt32(0));
                        cardType.setCT_cardType(rdr.GetInt32(1));
                        cardType.setCT_cardValue(rdr.GetInt32(2));
                        cardType.setCT_cardInfo(rdr.GetString(3));
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return cardType;
        }


        /// <summary>
        /// 根据cardType得到一个对象实体
        /// </summary>
        public Model.CardType GetModelByCardType(int cardType) 
        {
            Model.CardType cardTypeModel = new Model.CardType();
            string SQL_SELECT_CARDNUMBER = "SELECT * FROM CardType WHERE CT_cardType = @cardType";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@cardType", cardType);

                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_CARDNUMBER, para))
                {
                    if (rdr.Read())
                    {
                        cardTypeModel.setCT_cardTypeId(rdr.GetInt32(0));
                        cardTypeModel.setCT_cardType(rdr.GetInt32(1));
                        cardTypeModel.setCT_cardValue(rdr.GetInt32(2));
                        cardTypeModel.setCT_cardInfo(rdr.GetString(3));
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return cardTypeModel;
        }


        /// <summary>
        /// 得到所有CardType
        /// </summary>
        public List<Model.CardType> getTypeList() 
        {
            List<Model.CardType> cardTypeList = new List<Model.CardType>();
                       

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);
                string sqlStatement = "SELECT * FROM CardType WHERE 0 = 0";

                //Execute the query	
                SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sqlStatement);
                while (rdr.Read())
                {
                    Model.CardType cardTypeModel = new Model.CardType();
                    cardTypeModel.setCT_cardTypeId(rdr.GetInt32(0));
                    cardTypeModel.setCT_cardType(rdr.GetInt32(1));
                    cardTypeModel.setCT_cardValue(rdr.GetInt32(2));
                    cardTypeModel.setCT_cardInfo(rdr.GetString(3));

                    cardTypeList.Add(cardTypeModel);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return cardTypeList;
        }
    }
}
